package lib.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import object.Admin;
import object.LibraryManager;
import object.Reader;
import object.Students;
import object.Teachers;
import object.User;

public class UserOperator {

	// Here defines how to connector the database.
	private static final String connectionString = "jdbc:mysql://localhost:3306/library";
	// username for mysql, 'root' by default.
	private static final String dbUsername = "root";
	// user password for mysql, change to yours.
	private static final String dbPassword = "admin";

	static {
		try {
			// class name for mysql driver
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static User checkUser(String user_id, String passwd){
		String queryUser = "SELECT * FROM users WHERE user_id = ? && passwd = ?";
		String queryAdmin = "SELECT * FROM admin WHERE user_id = '" + user_id + "'";
		String queryLibrarian = "SELECT * FROM librarian WHERE user_id = '" + user_id + "'";
		String queryReader = "SELECT * FROM reader WHERE user_id = '" + user_id + "'";
		String queryTeacher = "SELECT * FROM teacher WHERE user_id = '" + user_id + "'";
		String queryStudent = "SELECT * FROM student WHERE user_id = '" + user_id + "'";
		
		try {
			Connection c = DriverManager.getConnection(connectionString,
					dbUsername, dbPassword);
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				// prepare the statement according to the SQL statement.
				ps = c.prepareStatement(queryUser);
				ps.setString(1, user_id);
				ps.setString(2, passwd);
				// Execute the Query
				rs = ps.executeQuery();
				if (!rs.next()){
					return null;	//no such user
				}
				User user = new User(user_id, passwd, rs.getString("user_name"), rs.getString("work_space"), rs.getString("user_phone"));
				
				ps = c.prepareStatement(queryAdmin);
				rs = ps.executeQuery();
				if (rs.next()){
					return new Admin(user.getUser_id(),
							user.getPassword(), 
							user.getUser_name(), 
							user.getWork_space(), 
							user.getUser_phone());
				}
				
				ps = c.prepareStatement(queryLibrarian);
				rs = ps.executeQuery();
				if (rs.next()){
					return new LibraryManager(user.getUser_id(),
							user.getPassword(), 
							user.getUser_name(), 
							user.getWork_space(), 
							user.getUser_phone(),
							rs.getString("library_name"));
				}
				
				ps = c.prepareStatement(queryReader);
				rs = ps.executeQuery();
				Reader reader;
				rs.next();
				reader = new Reader(user.getUser_id(),
							user.getPassword(), 
							user.getUser_name(), 
							user.getWork_space(), 
							user.getUser_phone(),
							rs.getInt("points"),
							rs.getInt("fine"),
							rs.getBoolean("is_frozen"));
				
				ps = c.prepareStatement(queryStudent);
				rs = ps.executeQuery();
				if (rs.next()){
					return new Students(user.getUser_id(),
							user.getPassword(), 
							user.getUser_name(), 
							user.getWork_space(), 
							user.getUser_phone(),
							reader.getPoints(),
							reader.getFine(),
							reader.is_frozen());
				}
				
				ps = c.prepareStatement(queryTeacher);
				rs = ps.executeQuery();
				return new Teachers(user.getUser_id(),
							user.getPassword(), 
							user.getUser_name(), 
							user.getWork_space(), 
							user.getUser_phone(),
							reader.getPoints(),
							reader.getFine(),
							reader.is_frozen());
				// get data line by line.
				// stops if rs.next() is false, which means no more lines available
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				// Notice! Always close the connection after using.
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
				if (c != null) {
					c.close();
				}
			}
			return null;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}

}
